package com.xoa.service.workflow.TerpServer.impl;

import com.xoa.dao.dataSrc.DataSrcMapper;
import com.xoa.dao.users.UsersMapper;
import com.xoa.dao.workflow.TerpServerMapper;
import com.xoa.model.dataSrc.DataSrc;
import com.xoa.model.workflow.TerpServer;
import com.xoa.service.workflow.TerpServer.TerpServerService;
import com.xoa.util.ToJson;
import com.xoa.util.common.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class TerpServerServiceImpl implements TerpServerService {

    @Autowired
    private TerpServerMapper terpServerMapper;

    @Autowired
    private UsersMapper usersMapper;

    @Autowired
    private DataSrcMapper dataSrcMapper;

    /**
     * //保存数据源
     * @param terpServer
     * @return
     */
    @Override
    public boolean insertTerpServer(TerpServer terpServer) {
        terpServer.setOldServerId("");
        terpServer.setDbServerRemark("");
        int i = terpServerMapper.insertSelective(terpServer);
        if (i>0){
            return true;
        }
        return false;
    }

    /**
     * 查询所有数据源名称
     * @return
     * @param terpServer
     */
    @Override
    public List<TerpServer> selectDbServerNameTerpServer(TerpServer terpServer) {
        return terpServerMapper.selectDbServerNameTerpServer(terpServer);
    }

    /**
     * 删除数据源配置
     * @param serverId
     * @return
     */
    @Override
    public boolean deleteTerpServer(Integer serverId) {
        return terpServerMapper.deleteTerpServer(serverId);
    }

    /**
     * 显示当前数据源下所有数据库名
     * @return
     */
    @Override
    public List<String> TerpServerShowDatabases() {
        return terpServerMapper.TerpServerShowDatabases();
    }

    /**
     * 通过serverId查询所有信息
     * @param serverId
     * @return
     */
    @Override
    public TerpServer TerpServerShowDatabasesByServerId(Integer serverId) {
        return terpServerMapper.TerpServerShowDatabasesByServerId(serverId);
    }

    /**
     * 显示当前数据库下所有表名
     * @param databaseName 数据库名
     * @return
     */
    @Override
    public List<String> TerpServerShowAllDatabaseName(String databaseName) {
        return terpServerMapper.TerpServerShowAllDatabaseName(databaseName);
    }

    /**
     * 显示表结构
     * @param tableName
     * @return
     */
    @Override
    public List<String> ShowTableStructure(String tableName) {
        return terpServerMapper.ShowTableStructure(tableName);
    }


    //创建表
    @Override
    public boolean CreateTerpServerTable(Map map) {
        int i = dataSrcMapper.CreateTerpServerTable(map);
        if (i==0){
            return true;
        }
        return false;
    }

    @Override
    public List<DataSrc> selectTerpServerByLike(Map map) {
        return dataSrcMapper.selectTerpServerByLike(map);
    }

    @Override
    public DataSrc findTableSize(Map map) {
        return dataSrcMapper.findTableSize(map);
    }

    @Override
    public boolean deleteTerpServerTwo(Integer dID) {
        int i = dataSrcMapper.deleteTerpServerTwo(dID);
        if (i>0){
            return true;
        }
        return false;
    }


    @Override
    public List<String> showColumns(Map<String, Object> map) {
        return dataSrcMapper.showColumns(map);
    }

   /* @Override
    public boolean alterTableField(Map map) {
        return dataSrcMapper.alterTableField(map);
    }*/

    /*@Override
    public boolean onlyYesAndNo(Map map) {
        return dataSrcMapper.onlyYesAndNo(map);
    }*/

  /*  @Override
    public boolean updataTerpServerField(Map map) {
        return dataSrcMapper.updataTerpServerField(map);
    }*/

    @Override
    public boolean dropTableField(Map map) {
        int i = dataSrcMapper.dropTableField(map);
        if (i>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean delectTableAllData(String tableName) {
        int i = dataSrcMapper.delectTableAllData(tableName);
        if (i>0){
            return true;
        }
        return false;
    }



    @Override
    public boolean addAdministrators(DataSrc dataSrc) {
        int i = dataSrcMapper.updateByPrimaryKeySelective(dataSrc);
        if (i>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean insertDataSrcNewtable(DataSrc dataSrc) {
        int i = dataSrcMapper.insertSelective(dataSrc);
        if (i>0){
            return true;
        }
        return false;
    }

    @Override
    public List<Map<String,String>> selectOneTableByTableName(String tableName) {
        return terpServerMapper.selectOneTableByTableName(tableName);
    }

    @Override
    public List<String> selectOneTableFieldByTableName(Map map) {
        return terpServerMapper.selectOneTableFieldByTableName(map);
    }

    @Override
    public boolean insertAnyOneTable(Map map1) {
        return terpServerMapper.insertAnyOneTable(map1);
    }


    @Override
    public ToJson editTerpServer(TerpServer terpServer) {
        ToJson toJson=new ToJson(1,"false");
        try {
            int i=terpServerMapper.updateByPrimaryKeySelective(terpServer);
            if (i>0){
                toJson.setMsg("成功");
                toJson.setCode("0");
            }
        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    @Override
    public int deleteTable(Integer dID) {
        try {
            DataSrc dataSrc = dataSrcMapper.selectByPrimaryKey(dID);
            if (dataSrc!=null){
                int i=dataSrcMapper.deleteTable("data_"+dataSrc.getdName());
                if (i>0){
                    return i;
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    //查询表是否存在
    @Override
    public int countTable(Map map) {
        return dataSrcMapper.countTable(map);
    }


    //查找内部数据源并返回表的结构
    @Override
    public DataSrc findByDataSrcId(HttpServletRequest request, Map map) {
        //查询内部数据源信息
        DataSrc dataSrc = dataSrcMapper.selectByPrimaryKey((Integer) map.get("dId"));
        if (dataSrc!=null){
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
            map.put("tableName","data_"+dataSrc.getdName());
            map.put("tableSchema",firstDBSource);
            //查询表结构
            dataSrc.setList(dataSrcMapper.findByDataSrcId(map));
        }
        return dataSrc;
    }

    //修改数据源信息
    @Override
    public boolean updataTerpServerById(DataSrc dataSrc) {
        int i = dataSrcMapper.updateByPrimaryKeySelective(dataSrc);
        if (i>0){
            return true;
        }
        return false;
    }


    //修改创建的表字段
    @Override
    @Transactional
    public boolean ALTERDataSrcTable( Map map) {
        List<Map> mapList=new ArrayList<>(); //要编辑的字段
        List<Map> mapList2=new ArrayList<>(); //要新增的字段

        List mapon=new ArrayList<>(); //要修改的索引
        List mapdelon=new ArrayList<>(); //要删除的索引
        String sqlType = (String) map.get("firstDBSource");
        String dName = (String) map.get("dName");

        Map map2=new HashMap();
        map2.put( "tableSchema" , sqlType );

        //判断库里是否存在这张表
        String dbName = dataSrcMapper.findDbName(sqlType, dName );
        if (!StringUtils.checkNull(dbName) && dbName.equals( dName )){
            map2.put( "tableName" , dbName );
        }


        for (DataSrc dataSrc:(List<DataSrc>) map.get("table")) {
            String data1 = dataSrc.getData1();
            String data2 = dataSrc.getData2();
            switch (data2){
                case "int":
                case "varchar":
                    break;
                default:
                    data2 = "";
                    break;
            }

            Map map1=new HashMap();
            String s = data1.replaceAll(" ", "");
            String str1 ="[0-9a-zA-Z_]{1,9}";
            if (str1.matches(s)) {
                map1.put( "data1" ,  s ); //字段名
            }
            map1.put( "data2" , data2 ); //字段类型
            map1.put( "data3" , dataSrc.getData3() ); //字段备注
            if (dataSrc.getIsnewedit()){ //编辑
                //没有修改的字段
                if (dataSrc.getOld()!=null){
                    map1.put("old",dataSrc.getOld()); //旧字段名
                    mapList.add(map1);

                    //判断是否修改了索引
                    if (dataSrc.getIsonly()){
                        for (Map<String,Object> map3:(List<Map<String, Object>>) dataSrcMapper.findByDataSrcId(map2)) {
                            if (map3.get("data1").equals(dataSrc.getOld()) && map3.get("data4").equals("UNI")){
                                    //要删除的索引
                                    mapdelon.add(dataSrc.getOld());
                                break;
                            }
                        }
                        //是否 唯一的索引
                        mapon.add(dataSrc.getData1());
                    }else{
                        for (Map<String,Object> map3:(List<Map<String, Object>>) dataSrcMapper.findByDataSrcId(map2)) {
                            if (map3.get("data1").equals(dataSrc.getOld()) && map3.get("data4").equals("UNI")){
                                //要删除的索引
                                mapdelon.add(dataSrc.getOld());
                                break;
                            }
                        }
                        //要删除的索引
                    }
                }
            }else { //新增
                mapList2.add(map1);
                if (dataSrc.getIsonly()) {
                    mapon.add(dataSrc.getData1());
                }
            }
        }
        if (mapList2.size()>0) {
            map.put("field",mapList2);
            int i = dataSrcMapper.insertField(map);//进行新增操作
        }
        if (mapList.size()>0){
            map.put("field",mapList);
            int i1 = dataSrcMapper.updataField(map);//进行修改操作
        }
        if (mapdelon.size()>0){
            map.put("delonly",mapdelon);
            dataSrcMapper.delIndex(map);
        }
        if (mapon.size()>0){
            map.put("only",mapon);
            //修改索引
            dataSrcMapper.updateIndex(map);
        }
        return true;
    }

    //公共查询表方法返回全部信息，传入表名
    @Override
    public List<Map<String, Object>> selectAll(Map map) {
        return dataSrcMapper.selectTableAll(map);
    }

    //公共的新增方法
    @Override
    public boolean insertData(Map map) {
        int i=dataSrcMapper.insertdata(map);
        if (i>0){
            return true;
        }
        return false;
    }

    //公共修改方法，传入表名和要修改的对象
    @Override
    public boolean updateData(Map map) {
        int i=dataSrcMapper.updateData(map);
        if (i>0){
            return true;
        }
        return false;
    }

    //删除字段方法
    @Override
    public int delField(Map map) {
        return dataSrcMapper.delField(map);
    }

    //外部数据源的编辑方法
    @Override
    public boolean updataTerpById(TerpServer terpServer) {
        int i = terpServerMapper.updateByPrimaryKeySelective(terpServer);
        if (i>0){
            return true;
        }
        return false;
    }

    //导入功能
    @Override
    public ToJson terpServerImportExcel(HttpServletRequest request, HttpServletResponse response, MultipartFile file, HttpSession session, String tableName) {
        ToJson toJson=new ToJson(1,"false");
        try {
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");

            ResourceBundle rb = ResourceBundle.getBundle("upload");
            String osName = System.getProperty("os.name");
            StringBuffer path = new StringBuffer();
            if (osName.toLowerCase().startsWith("win")) {
                //sb=sb.append(rb.getString("upload.win"));
                //判断路径是否是相对路径，如果是的话，加上运行的路径
                String uploadPath = rb.getString("upload.win");
                if (uploadPath.indexOf(":") == -1) {
                    //获取运行时的路径
                    String basePath = this.getClass().getClassLoader().getResource(".").getPath() + File.pathSeparator;
                    //获取截取后的路径
                    String str2 = "";
                    if (basePath.indexOf("/xoa") != -1) {
                        //获取字符串的长度
                        int length = basePath.length();
                        //返回指定字符在此字符串中第一次出现处的索引
                        int index = basePath.indexOf("/xoa");
                        //从指定位置开始到指定位置结束截取字符串
                        str2 = basePath.substring(0, index);
                    }
                    path = path.append(str2 + "/xoa");
                }
                path.append(uploadPath);
            } else {
                path = path.append(rb.getString("upload.linux"));
            }
            //判断文件是否存在
            if (file.isEmpty()) {
                toJson.setMsg("请上传文件");
                return toJson;
            } else {
                String fileName = file.getOriginalFilename();
                //判断是否是指定文件类型
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File dest = new File(path.toString(), newFileName);
                    InputStream in = file.getInputStream();
                    HSSFWorkbook excelObj = new HSSFWorkbook(in);
                    HSSFSheet sheetObj = excelObj.getSheetAt(0);
                    Row row = sheetObj.getRow(0); //获取第0行
                    if (row!=null){
                        int colNum = row.getPhysicalNumberOfCells(); //列
                        int lastRowNum = sheetObj.getLastRowNum();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Map map=new HashMap();
                        map.put("tableSchema",firstDBSource); //所在库


                        //判断库里是否存在这张表
                        String dbName = dataSrcMapper.findDbName(firstDBSource , "data_" + tableName);
                        if (!StringUtils.checkNull(dbName) && dbName.equals("data_" + tableName)){
                            map.put("tableName" , dbName ); //表名
                        }

                        //查询所在表的字段
                        List byDataSrcId = dataSrcMapper.findByDataSrcId(map);
                        for (int i = 0; i <= lastRowNum; i++) {
                            row = sheetObj.getRow(i); //行数据
                            if (row != null) {
                                Map map1=new HashMap(); //要新增的数据对象
                                for (int j = 1; byDataSrcId.size()>j; j++) {
                                    Cell cell = row.getCell(j-1);
                                    Map map2 = (Map) byDataSrcId.get(j);
                                    cell.setCellType(CellType.STRING);

                                    String key = (String) map2.get("data1");

                                    String dbTabName = dataSrcMapper.findDbTabName( firstDBSource , dbName , key );
                                    if (StringUtils.checkNull(dbTabName) || !dbTabName.equals(key)){

                                        map1.put( dbTabName , cell.getStringCellValue() );

                                    }

                                }
                                if (map1.size()>0){

                                    //要新增的数据
                                    map.put("tabObj",map1);

                                    //进行新增操作
                                    dataSrcMapper.insertdata(map);

                                }
                            }else{
                                continue;
                            }
                        }
                    }else{
                        toJson.setMsg("请从第一行开始录入");
                    }
                }
            }

        }catch (Exception e){
            e.printStackTrace();
            toJson.setMsg(e.toString());
        }
        return toJson;
    }

    //查询库中所有表结构
    @Override
    public List findtablAll(String firstDBSource) {
        //String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
        return dataSrcMapper.firstDBSource(firstDBSource);
    }

    @Override
    public String findDbName(String name,String tabName) {
        return dataSrcMapper.findDbName(name,tabName);
    }

    @Override
    public String findDbTabName(String sqlType,String TabName,String Column) {
        return dataSrcMapper.findDbTabName(sqlType,TabName,Column);
    }

    //查询表中所有字段信息
    @Override
    public List findTableField(Map map) {
        return dataSrcMapper.findByDataSrcId(map);
    }


    //根据ID查询terp
    @Override
    public TerpServer selectTerpById(Integer serverId) {
        return terpServerMapper.selectByPrimaryKey(serverId);
    }

    //查询表结构
    @Override
    public ToJson getTabField(HttpServletRequest request, String tableName) {
        ToJson toJson=new ToJson(1,"err");
        try {
            String firstDBSource = "xoa" + (String) request.getSession().getAttribute("loginDateSouse");
            Map map=new HashMap();
            map.put("tableSchema",firstDBSource);
            map.put("tableName",tableName);
            List byDataSrcId = dataSrcMapper.findByDataSrcId(map);
            if (byDataSrcId.size()>0){
                toJson.setMsg("ok");
                toJson.setFlag(0);
                toJson.setObj(byDataSrcId);
            }
        }catch (Exception e){
            toJson.setMsg(e.getMessage());
        }

        return toJson;
    }


}
